*-----------------------------------------------------------------
version 10
cap clear
cap log close
set more off
cd "E:\REStat_MS14767_Vol96(2)\Data preparation Compustat"
*log using "4_RJV_panel.log", replace
*-----------------------------------------------------------------

set mem 700m
set mat 2000

**********************************************************************
*** This file generates a basic dataset combining information from ***
*** compustat and rjv data. All compustat firms remain in the      ***
*** dataset whereas rjvs without ticker drop out 		   ***
**********************************************************************

use "raw data\JV180506_corrected_ticker.dta"
drop multiple sic2 count
so  entityname
merge entityname using "raw data\JV180506_corrected_ticker_nonprofit.dta"
drop _merge inset2 


**********************************************************************
* correct ticker for some companies
* the ticker variable of this dataset has been compared to the one
* in the compustat data. Here tickers are changed in order to
* make connections obvious.
**********************************************************************

replace ticker="ADEP" if ticker=="3ADEO"   /*Adept Technology, Inc.		*/
replace ticker="AMPX" if ticker=="3AEXCA" /*Ampex Corp.                     */
replace ticker="CMDZQ" if ticker=="3CMDZE" /*Comdial Corporation            */
replace ticker="DVW" if ticker=="3COVD"  /*Covad Communications Group, Inc. */
replace ticker="PRAC" if ticker=="3PRAC"  /*Productivity Technologies Corp. */
replace ticker="TGIS" if ticker=="3TGIS"  /*Thomas Group, Inc. 			*/
replace ticker="3DVNTF" if ticker=="9142B" /* Diversinet Corp.			*/
replace ticker="ADCT" if ticker=="ADCTD"  /*ADC Telecommunications, Inc.    */
replace ticker="AEIS" if ticker=="AEISE"  /*Advanced Energy Industries, Inc.*/
replace ticker="AOLAQ" if ticker=="AOLA" /* America Online, Inc.			*/
replace ticker="BCE1" if ticker=="BCE1."  /*Bell Canada                     */
replace ticker="CDNS" if ticker=="CDN"    /*Cadence Design Systems, Inc.    */
replace ticker="CIC." if ticker=="CERTF" /* Certicom Corp.				*/
replace ticker="3CRMT" if ticker=="CK"    /*Crompton Corp.                  */
replace ticker="CRWN" if ticker=="CRWNE"  /*Crown Media Holdings, Inc.      */
replace ticker="CSPIE" if ticker=="CSPI"   /*CSP, Inc.                      */
replace ticker="DMRC" if ticker=="DMRCE"  /*Digimarc Corp.                  */
replace ticker="3EXBY" if ticker=="3EXBT"	/* Exabyte Corp.				*/
replace ticker="3PCOM." if ticker=="3PCMC" /* P-Com, Inc.				*/
replace ticker="GSIG" if ticker=="GSLI"	/* GSI Group Inc.				*/
replace ticker="FON.R" if ticker=="FON"    /*Sprint Corp.         	        */
replace ticker="0780B" if ticker=="HTN."   /*Houghton Mifflin Co.             */
replace ticker="3IMCI" if ticker=="IMCI"   /*Infinite Group, Inc.             */
replace ticker="KAMN" if ticker=="KAMNA"  /*Kaman Corp.                       */
replace ticker="NHYB" if ticker=="KSRC"   /*Kendall Square Research Corp.     */
replace ticker="LORL" if ticker=="LRLSQ"  /*Loral Space & Communications Corp.*/
replace ticker="NER.3" if ticker=="NER" 	/* Newcor, Inc.				*/
replace ticker="NGH." if ticker=="NGH"	/* Nabisco Group Holding		*/
replace ticker="NIF.U" if ticker=="NRD"	/* Noranda, Inc.				*/
replace ticker="0782B" if ticker=="NTK"    /*Nortek, Inc.                     */
replace ticker="3NTPA" if ticker=="NTPA"   /*Netopia Inc.                     */
replace ticker="PCZ" if ticker=="PCA."	/* Petro-Canada Inc.			*/
replace ticker="SPM.3" if ticker=="SPM."	/*Saga Petroleum Asa	 		*/
replace ticker="SJR.NV.B" if ticker=="SJR" /* Shaw Communications Inc.		*/
replace ticker="3SGID" if ticker=="SGI"    /*Silicon Graphics, Inc.           */
replace ticker="SMU.1" if ticker=="SMU"    /*Simula, Inc.                     */
replace ticker="SYN.1" if ticker=="SYN"    /*Syntex Corp.                     */
replace ticker="TFSI" if ticker=="TFS"    /*Three-Five Systems, Inc.          */
replace ticker="WXCP" if ticker=="WHXCQ"  /*WHX Corp.                         */
replace ticker="LSMJ" if ticker=="XSYS"   /*XXsys Technologies, Inc.          */


**********************************************************************
* for the following companies observations will be dropped after
* merging the datasets, because no counterpart could be traced in
* the compustat data. Sometimes there are companies with similar
* names in the compustat data, but whether they actually are the
* same remains unclear. In particular, AOL is foud in the compustat
* data as "AMERICA ONLNE LTN AMR  -CL A" and ticker "AOLAQ". Here, the ticker numer and
* the name as given in "entityname" are listed;
*4135B   ABKCO Music & Records
*HI      Household International, Inc.
*LCO.    Louisville Cement Co.
*LYON.   Lyon Metal Products, Inc.
*PCSI.   Paragon Communications Systems
*SBC SBC Communications, Inc.
*SNGR    Singer Co. N.V.
*SNTEQ   Santec Corp.
*WEPC    West Chemical Products, Inc.
**********************************************************************

egen rjvnum=group(rjvname)
egen comnum=group(entityname)

so rjvnum
save "RJV files\insiders_observed.dta", replace


**********************************************************************
* here we generate a panel at the RJV level, which still contains all firms information
* we do this in order to build variables related to the size of RJV per year (nfirmP)
* the number of compustat firms in a RJV per year (nfirmCOMP)
* as well as the number of firms in the same SIC4 industry per year (nfirmPsame)
**********************************************************************

use "RJV files\insiders_observed.dta", clear


*************
* blow it up to a panel for 1985 - 1999
*************

gen year=1985
save "RJV files\rjv1985.dta", replace
local i=85
forvalues i=86/99 {
replace year=19`i'
save "RJV files\rjv19`i'.dta", replace
}
use "RJV files\rjv1985.dta", clear
append using "RJV files\rjv1986.dta"
append using "RJV files\rjv1987.dta"
append using "RJV files\rjv1988.dta"
append using "RJV files\rjv1989.dta"
append using "RJV files\rjv1990.dta"
append using "RJV files\rjv1991.dta"
append using "RJV files\rjv1992.dta"
append using "RJV files\rjv1993.dta"
append using "RJV files\rjv1994.dta"
append using "RJV files\rjv1995.dta"
append using "RJV files\rjv1996.dta"
append using "RJV files\rjv1997.dta"
append using "RJV files\rjv1998.dta"
append using "RJV files\rjv1999.dta"
sort rjvnum year comnum

desc

save "raw_rjv_panel.dta", replace

*log close


